<?php
/**
* @version		$Id: dynl_database.php 1 2007-01-11 22:03:46Z friesengeist $
* @package		Dynlist
* @copyright	Copyright (C) 2005 - 2007 Dynlist. All rights reserved.
* @license		GNU/GPL, see LICENSE.php
* Dynlist is free software. This version may have been modified pursuant
* to the GNU General Public License, and as distributed it includes or
* is derivative of works licensed under the GNU General Public License or
*base.php  other free or open source software licenses.
* See COPYRIGHT.php for copyright notices and details.
*/
/**
* Dynlist database class
*
* Provide many supporting of database access.
*
* @package		Dynlist
* @final
*/
class dynl_database
{
	/**
	 * Host name where Dynlist informations are stored.
	 *
	 * @var char
	 * @access protected
	 */
	var $_hostname = null;

	/**
	 * Database user.
	 *
	 * @var char
	 * @access protected
	 */
	var $_user = null;

	/**
	 * Database password.
	 *
	 * @var char
	 * @access protected
	 */
	var $_password = null;

	/**
	 * Database name where Dynlist informations are stored.
	 *
	 * @var char
	 * @access protected
	 */
	var $_dbname = null;

	/**
	 * Database table name where data is stored.
	 *
	 * @var char
	 * @access protected
	 */
	var $_tabname = null;
	
	/**
	 * List of fields to recover or update.
	 *
	 * @var char
	 * @access protected
	 */
	var $_fields = null;

	/**
	 * Filter used to retrieve or update data
	 *
	 * @var char
	 * @access protected
	 */
	var $_filter = null;
	
	/**
	 * Order By fields
	 *
	 * @var char
	 * @access protected
	 */
	var $_orderby = null;

	/**
	 * Connection to database.
	 *
	 * @var char
	 * @access protected
	 */
	var $_connection = null;
	
	/**
	 * SQL command setted by children classes to commit or retrieve data from database.
	 *
	 * @var char
	 * @access protected
	 */
	var $_sql_command = null;
			
	/**
	* Class constructor
	*
	* @access protected
	* @param char hostname 
	* @param char dbname
	* @param char dbtable
	*/
	function dynl_database($tabname)
	{
		$this->_hostname = "localhost";
		$this->_user = "proj";
		$this->_password = "projetos";
		$this->_dbname = "dynlist";
		$this->_tabname = $tabname;
	}

	/**
	* Connect to database.
	*
	* @access protected
	 */
	function connect()
	{
		//Message errors
		$msg[0] = "Unable to connect to DB server";
		$msg[1] = "Unable to select to DB server";

        //try { 
            //$this->result = $this->db->Execute($sql);
	 		$this->_connection = mysql_pconnect($this->_hostname, $this->_user, $this->_password) or die($msg[0].mysql_error());
			mysql_select_db($this->_dbname,$this->_connection) or die($msg[1]);	
       	//} catch (Exception $e) {
        //    echo "Erro na pesquisa:<br>Erro:".$e->getMessage().'<br>SQL:'.$sql.'<br><a href="javascript:history.go(-1)">Voltar</a>';
        //}
 	}

 	/**
	* Get columns of table.
	*
	* @access protected
	* @return columns
	*/
	function get_columns()
	{
		$result = mysql_query("SHOW COLUMNS FROM ".$this->_tabname);
		$count = 0;
		while ($row=mysql_fetch_row($result)){
   			$cnt = 0;
  			foreach ($row as $item){
       			if ($cnt == 0){
           			$columns[$count] = $item;
          		 	$cnt++;
         		  	$count++;
      			}
   			}
		}
		
		return $columns;
		
	}
 	 	
	/**
	* Set SQL Command to retrieve Dynlist Data information from database.
	*
	* @access protected
	*/
	function set_sql_retrieve()
	{
		
		$sql_command = 'SELECT ';
		
		// Set fields to retrieve.
		if ($this->_fields == 'all') {
			$sql_command .= '*';
		} else {
			for ($i=0; $i < count($this->_fields); $i++){		
				$sql_command .= $this->_fields[$i];
				// Only adds comma to separate fields.
				if ($i < (count($this->_fields) - 1)){
					$sql_command .= ',';
				}
			}
		}

		// Set source.
		$sql_command .= ' FROM '.$this->_tabname;
		
		// Set filter.
		if ($this->_filter <> 'all'){
			$sql_command .= ' WHERE ';
			for ($i=0; $i < count($this->_filter); $i=$i+2){
				// field name.
				$sql_command .= $this->_filter[$i]."='";
				// value.
				$sql_command .= $this->_filter[$i+1]."'";

				if ($i < (count($this->_filter) - 2)) {
					$sql_command .= " AND ";
				} 
			
			}
		}
		
		// Set order by clause.
		if ($this->_orderby <> 'none'){
			$sql_command .= ' ORDER BY ';
			foreach ($this->_orderby as $h_orderby) {
				$sql_command .= $h_orderby." ";
			}
		}

		$this->_sql_command = $sql_command;
		//print_r($this->_sql_command);
		//echo 'filter-->';
		//print_r($this->_filter);
	}

	
	/**
	* Retrieve data from database using SQL command.
	* Receive 2 parameters:
	* fields --> An array with all fields that must be returned
	* filter --> An array with field name and value to filter
	*
	* Returns array with data. Each line of this array is record.
	* First line is the columns label.
	*
	* @access protected
	* @param array fields
	* @param array fielter
	* @param array orderby
	* @return array data
	*/
	function retrieve($fields,$filter,$orderby)
	{
		// Set fields to select.		
		$this->_fields = $fields;
		
		// Set filter to select.
		$this->_filter = $filter;

		// Set order by clause.
		$this->_orderby = $orderby;

		// Set SQL command.
		$this->set_sql_retrieve();
		
		// Get values from catalog in DB.
		$this->connect();
		mysql_select_db($this->_dbname,$this->_connection) or die($msg[1]);	
		$query_result = mysql_query($this->_sql_command,$this->_connection);
		
		// Get columns of table;
		if ($this->_fields == 'all') {
			$columns = $this->get_columns();	
		} else {
			$columns = $this->_fields;
		}
		
		// First line is the columns label.
		$data[0] = $columns;
				
		$i = 1;
		while ($record = mysql_fetch_array($query_result)) {
			// Mount an array with data.
			for ($j=0; $j <= count($columns); $j++){
				$data_record[$j] = $record[$columns[$j]];	
			}
			// Append item on data array.
			$data[$i] = $data_record;
			$i++;
		}
 		
		// Release memory.
		mysql_free_result($query_result);
	
		// Return result.
		return $data;
		
	}

	/**
	* Update data on database.
	*
	* @access protected
	*/
	function update($fieldkeys,$setvalues)
	{
		// Define table name.
		$sql_table = 'INSERT INTO '.$this->_tabname;
		
		// Define fields to be updated.
		foreach($setvalues as $setvalue)
		{
			if ($setvalue[0] <> '')
			{
				if ($sql_fields <> '')
				{
					$sql_fields .= ',';
					$sql_values .= ',';
				}
				$sql_fields .= $setvalue[0];
				
				if ($setvalue[1] == '')
				{ 
					$sql_values .= "''";
				} else {
					$sql_values .= "'".$setvalue[1]."'";
				}
				
				// If fieldname is not part of key, include it into UPDATE clause.
				if (!in_array($setvalue[0],$fieldkeys)){
					if ($sql_update <> '')
					{
						$sql_update .= ',';
					}
					$sql_update .= $setvalue[0]."='".$setvalue[1]."'"; 
				}
			}
		}
		
		$sql_command .= $sql_table.' ('.$sql_fields.') VALUES ('.$sql_values.') ';
		$sql_command .= 'ON DUPLICATE KEY UPDATE '.$sql_update;
		
		//echo $sql_command;
		// Update values into DB.
		$this->connect();
		mysql_select_db($this->_dbname,$this->_connection) or die($msg[1]);	
		mysql_query($sql_command,$this->_connection);
	}
	
	/**
	* Delete record from database.
	*
	* @access protected
	*/
	function delete($filter)
	{
		// Define table.
		$sql_table .= "DELETE FROM ".$this->_tabname;

		// Define WHERE clause.
		for ($i = 0; $i < count($filter); $i = $i + 2)
		{
			if ($sql_where <> ''){
				$sql_where .= " AND ";
			}
			$sql_where .= $filter[$i]."='".$filter[$i+1]."'";
		}

		$sql_command = $sql_table." WHERE ".$sql_where;
		print_r($sql_command);
		
		// Update values into DB.
		$this->connect();
		mysql_select_db($this->_dbname,$this->_connection) or die($msg[1]);	
		mysql_query($sql_command,$this->_connection);
	}
	
	/**
	* Update data on database.
	*
	* @access protected
	*/
	/*
	function update1($fieldkeys,$setvalues)
	{
		// Define table name.
		$sql_table = 'UPDATE '.$this->_tabname.' SET ';
		
		// Define fields to be updated.
		foreach($setvalues as $setvalue)
		{
			if ($setvalue[0] <> '')
			{
				if ($sql_fields <> '')
				{
					$sql_fields .= ',';
				}
					$sql_fields .= $setvalue[0]."='".$setvalue[1]."'";
			}

			// If fieldname is part of key, include it into WHERE clause.
			if (in_array($setvalue[0],$fieldkeys)){
				if ($sql_where <> '')
				{
					$sql_where .= ' AND ';
				}
				$sql_where .= $setvalue[0]."='".$setvalue[1]."'";
			}
		}
		
		// Define record to be updated.
		$sql_where = " WHERE ".$sql_where;
		$sql_command .= $sql_table.$sql_fields.$sql_where;
		
		echo $sql_command;
		// Update values into DB.
		//$this->connect();
		//mysql_select_db($this->_dbname,$this->_connection) or die($msg[1]);	
		//mysql_query($sql_command,$this->_connection);
	}
	*/
}
?>
